import { NextRequest, NextResponse } from 'next/server';
import Database from 'better-sqlite3';
import path from 'path';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

interface PBSLevel {
  id: number;
  level1_area: string;
  level2_category: string | null;
  level3_subitem: string;
  full_path: string;
  pbs_code: string;
  created_at: string;
}

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const area = searchParams.get('area');
    const search = searchParams.get('search');

    const db = new Database(DB_PATH);
    
    let query = `
      SELECT * FROM pbs_levels 
      WHERE 1=1
    `;
    const params: any[] = [];

    // 按区域过滤
    if (area) {
      query += ` AND level1_area = ?`;
      params.push(area);
    }

    // 按搜索词过滤
    if (search) {
      query += ` AND (
        full_path LIKE ? OR 
        pbs_code LIKE ? OR 
        level1_area LIKE ? OR 
        level2_category LIKE ? OR 
        level3_subitem LIKE ?
      )`;
      const searchPattern = `%${search}%`;
      params.push(searchPattern, searchPattern, searchPattern, searchPattern, searchPattern);
    }

    query += ` ORDER BY level1_area, level2_category, level3_subitem`;

    const stmt = db.prepare(query);
    const levels = stmt.all(...params) as PBSLevel[];
    
    db.close();

    return NextResponse.json({
      success: true,
      data: levels,
      message: `找到 ${levels.length} 条PBS层级记录`
    });

  } catch (error) {
    console.error('获取PBS层级失败:', error);
    return NextResponse.json(
      { 
        success: false, 
        error: '获取PBS层级失败',
        details: error instanceof Error ? error.message : '未知错误'
      },
      { status: 500 }
    );
  }
}

// 获取所有区域列表
export async function POST(request: NextRequest) {
  try {
    const body = await request.json();
    const { action } = body;

    if (action === 'getAreas') {
      const db = new Database(DB_PATH);
      
      const stmt = db.prepare(`
        SELECT DISTINCT level1_area as area, COUNT(*) as count
        FROM pbs_levels 
        GROUP BY level1_area 
        ORDER BY level1_area
      `);
      const areas = stmt.all();
      
      db.close();

      return NextResponse.json({
        success: true,
        data: areas,
        message: `找到 ${areas.length} 个区域`
      });
    }

    return NextResponse.json(
      { success: false, error: '不支持的操作' },
      { status: 400 }
    );

  } catch (error) {
    console.error('处理请求失败:', error);
    return NextResponse.json(
      { 
        success: false, 
        error: '处理请求失败',
        details: error instanceof Error ? error.message : '未知错误'
      },
      { status: 500 }
    );
  }
}